package com.glc.Dao;

import com.glc.entity.StandingBook;
import com.glc.utils.DateUtil;
import com.glc.utils.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

/**
 * 仓库出入记录的数据库层类
 */
public class StandingBookDao {
//    public static void main(String[] args) {
//        StandingBook standingBook=new StandingBook(2,"glc",null,null,2.00,2.00,2,new Date());
////        System.out.println(new StandingBookDao().addOneRecord(standingBook));
////        System.out.println(new StandingBookDao().deleteRecordById(1));
////        System.out.println(new StandingBookDao().updateRecordById(standingBook));
////        System.out.println(new StandingBookDao().queryRecordById(2));
//        StandingBook standingBook1=new StandingBook();
//        standingBook1.setMname("glc");
//        standingBook1.setMcount(4);
//        List<StandingBook> list=new StandingBookDao().queryRecordByCondition(standingBook1);
//        for (StandingBook sb:list){
//            System.out.println(sb.toString());
//        }
//    }
    //该类的一个实例
    static final StandingBookDao STANDING_BOOK_DAO=new StandingBookDao();

    //只能通过get方法获取，这样实现单例
    public static StandingBookDao getStandingBook() {
        return STANDING_BOOK_DAO;
    }

    //构造函数私有化
    private StandingBookDao() {
    }

    /**
     * 增加一条仓库记录
     * @param standingBook 记录
     * @return 插入记录是否成功
     */
    public boolean addOneRecord(Connection conn,StandingBook standingBook){
        PreparedStatement ps=null;
        String sql="insert  into standing_book values ("+standingBook.getId()+",'"+standingBook.getMname()+"',"
                + standingBook.getStoreId()+","+standingBook.getMcount()+","+standingBook.getMprice()+","+standingBook.getMsum()+",'"
                +standingBook.getType()+ "','"+DateUtil.toTimeString(standingBook.getTime())+"')";
        try {
            ps=conn.prepareStatement(sql);
            return ps.executeUpdate()==1;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return false;
    }
    public boolean deleteRecordById(Integer id){
        Connection connection= JDBCUtil.getConnection();
        PreparedStatement ps=null;
        String sql="delete from standing_book where id=?";
        try {
            ps= connection.prepareStatement(sql);
            ps.setInt(1,id);
            return ps.executeUpdate()==1;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return false;
    }
    public boolean updateRecordById(StandingBook standingBook){
        Connection connection= JDBCUtil.getConnection();
        PreparedStatement ps=null;
        StringBuilder sb=new StringBuilder("update standing_book set");
        if (standingBook.getMname()!=null){
            sb.append(" mname='"+standingBook.getMname()+"',");
        }
        if(standingBook.getMcount()!=null){
            sb.append("mcount="+standingBook.getMcount()+',');
        }
        if(standingBook.getMprice()!=null){
            sb.append("mprice="+standingBook.getMprice()+',');
        }
        if(standingBook.getMsum()!=null){
            sb.append("msum="+standingBook.getMsum()+',');
        }
        if(standingBook.getStoreId()!=null){
            sb.append("store_id="+standingBook.getStoreId()+',');
        }
        if(standingBook.getTime()!=null){
            sb.append("time='"+DateUtil.toTimeString(standingBook.getTime())+"',");
        }
        if(standingBook.getType()!=null){
            sb.append("type="+standingBook.getType()+',');
        }
        sb.deleteCharAt(sb.length()-1);
        sb.append(" where id="+standingBook.getId());
        try {
            ps= connection.prepareStatement(sb.toString());
            return ps.executeUpdate()==1;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return false;
    }
    public StandingBook queryRecordById(Integer id){
        Connection connection= JDBCUtil.getConnection();
        PreparedStatement ps=null;
        String sql="select * from standing_book where id="+id;
        try {
            ps= connection.prepareStatement(sql);
            ResultSet rs=ps.executeQuery();
            rs.next();
            return new StandingBook(rs.getInt("id"),rs.getString("mname"),rs.getInt("store_id"),
                    rs.getDouble("mcount"),rs.getDouble("mprice"),rs.getDouble("msum"),
                    rs.getString("type"),rs.getDate("time"));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    };
    public List<StandingBook> queryRecordByCondition(StandingBook standingBook){
        Connection connection= JDBCUtil.getConnection();
        PreparedStatement ps=null;
        StringBuilder sb=new StringBuilder("select * from standing_book where 1=1"+standingBook.queryString());
        List<StandingBook> result=new LinkedList<>();
        try {
            ps= connection.prepareStatement(sb.toString());
            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                result.add(new StandingBook(rs.getInt("id"),rs.getString("mname"),rs.getInt("store_id"),
                        rs.getDouble("mcount"),rs.getDouble("mprice"),rs.getDouble("msum"),
                        rs.getString("type"),rs.getDate("time")));
            }
            return result;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    };
}
